﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data.SqlClient;
using System.Data;

/// <summary>
/// Summary description for tblAccount
/// </summary>
public class tblAccount
{
    ConnectionDB connect = new ConnectionDB();
    SqlConnection conn;
    SqlCommand cmd;
	public tblAccount()
	{
		 conn = connect.getConnect();
	}
    public bool CheckLogin(string user, string pass)
    {

        if (conn.State == ConnectionState.Closed)
            conn.Open();
        string sql = "select * from tblUser where UserName=@user and Password=@pass";
        cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@user", user);
        cmd.Parameters.AddWithValue("@pass", pass);
        SqlDataReader data = cmd.ExecuteReader();
        bool result = data.HasRows;
        conn.Close();
        return result;
    }
    public bool changpass(string pass, string name,int id)
    {
        if (conn.State == ConnectionState.Closed)
            conn.Open();
        string sql = "update tblUser set UserName=@user,Password=@pass where Id=@id ";
        cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@user", name);
        cmd.Parameters.AddWithValue("@pass", pass);
        cmd.Parameters.AddWithValue("@id", id);
        int n = cmd.ExecuteNonQuery();
        bool result = (n > 0);
        conn.Close();
        return result;
    }
    public bool getPassword(string name, string pass)
    {

        if (conn.State == ConnectionState.Closed)
            conn.Open();
        string sql = "select * from tblUser where UserName=@user and  Password=@pass ";
        cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@user", name);
        cmd.Parameters.AddWithValue("@pass", pass);
        SqlDataReader data = cmd.ExecuteReader();
        bool result = data.HasRows;
        conn.Close();
        return result;

    }
    public DataTable getAccountById(string user , string pass)
    {

        if (conn.State == ConnectionState.Closed)
            conn.Open();
        string sql = "select * from tblUser where UserName=@user and  Password=@pass";
        cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@user", user);
        cmd.Parameters.AddWithValue("@pass", pass);
        DataTable table = new DataTable();
        SqlDataAdapter data = new SqlDataAdapter(cmd);
        data.Fill(table);
        conn.Close();
        return table;
    }
    public DataTable getCode()
    {

        if (conn.State == ConnectionState.Closed)
            conn.Open();
        string sql = "  select a.Code,a.Id,a.Name,b.Code,b.IsRelease from tblCardType a , tblCard b where a.Code = b.CardTypeCode";
        cmd = new SqlCommand(sql, conn);
        DataTable table = new DataTable();
        SqlDataAdapter data = new SqlDataAdapter(cmd);
        data.Fill(table);
        conn.Close();
        return table;
    }
    //public DataTable getCount(int code)
    //{

    //    if (conn.State == ConnectionState.Closed)
    //        conn.Open();
    //    string sql = "  select count(a.Id) as CountID   from tblCardType a , tblCard b where a.Code = b.CardTypeCode and a.Code=@code ";
    //    cmd = new SqlCommand(sql, conn);
    //    cmd.Parameters.AddWithValue("@code",code);
    //    DataTable table = new DataTable();
    //    SqlDataAdapter data = new SqlDataAdapter(cmd);
    //    data.Fill(table);
    //    conn.Close();
    //    return table;
    //}
    public DataTable getCountbyIsRelease(int code)
    {

        if (conn.State == ConnectionState.Closed)
            conn.Open();
        string sql = "  select count(a.Id) as CountID   from tblCardType a , tblCard b where a.Code = b.CardTypeCode and a.Code=@code and b.IsRelease=0 ";
        cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@code", code);
        DataTable table = new DataTable();
        SqlDataAdapter data = new SqlDataAdapter(cmd);
        data.Fill(table);
        conn.Close();
        return table;
    }
    public DataTable getCountbyRelease(int code)
    {

        if (conn.State == ConnectionState.Closed)
            conn.Open();
        string sql = "  select count(a.Id) as CountID   from tblCardType a , tblCard b where a.Code = b.CardTypeCode and a.Code=@code and b.IsRelease=1 ";
        cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@code", code);
        DataTable table = new DataTable();
        SqlDataAdapter data = new SqlDataAdapter(cmd);
        data.Fill(table);
        conn.Close();
        return table;
    }
}